
--BABY insert Test--
insert into babyData(babyNum, suyu, sobyun, play, inputdate, suyuamount,
liyusik, bochaegi, ungga, sleeptime, diapers, starttime, memo
) 
values(babyData_seq.nextval, 6, 5, 2, to_date('2014-10-22','YYYY-MM-DD'), 90,
	4, 5, 1, 50, 1, to_date('2014/10/22 15:07:44','YYYY/MM/DD HH24:MI:SS'), 'memo function'
);

insert into babyData(babyNum, suyu, sobyun, play, inputdate, suyuamount,
liyusik, bochaegi, ungga, sleeptime, diapers, starttime, memo
) 
values(babyData_seq.nextval, 4, 3, 1, to_date('2014-10-25','YYYY-MM-DD'), 90,
	4, 5, 1, 50, 1, to_date('15:07:44','HH24:MI:SS'), 'memo function'
);


--BABY select *--
select * from babyData;

delete from babyData where babyNum>0;

select * from babyData where rownum = 1 order by babyNum desc;


----------------- DROP TABLE AND SEQUENCE -----------------
DROP TABLE MAMMAMEMBER;
DROP TABLE BABY;
DROP TABLE ADMINCHECK;
DROP TABLE BABYINFO;
DROP TABLE FRIENDS;
DROP TABLE INFORMATION;
DROP TABLE CALENDAR;
DROP TABLE VACCINATION;
DROP TABLE PHOTO;
DROP TABLE PHOTOCOMMENT;
DROP TABLE SALEBOARD;
DROP TABLE SALEBOARDCOMMENT;
DROP TABLE BOARD;
DROP TABLE BOARDCOMMENT;
DROP TABLE UPDATES;

DROP SEQUENCE BABY_SEQ;
DROP SEQUENCE ADMINCHECK_SEQ;
DROP SEQUENCE BABYINFO_SEQ;
DROP SEQUENCE INFORMATION_SEQ;
DROP SEQUENCE CALENDAR_SEQ;
DROP SEQUENCE VACCINATION_SEQ;
DROP SEQUENCE PHOTO_SEQ;
DROP SEQUENCE PHOTOCOMMENT_SEQ;
DROP SEQUENCE SALEBOARD_SEQ;
DROP SEQUENCE SALEBOARDCOMMENT_SEQ;
DROP SEQUENCE BOARD_SEQ;
DROP SEQUENCE SEQUENCE BOARDCOMMENT_SEQ;
DROP SEQUENCE UPDATES_SEQ;

----------------- member ------------------

CREATE TABLE MAMMAMEMBER
(
	EMAIL 			VARCHAR2(25) 						CONSTRAINT MM_EM_PK 	PRIMARY KEY 
	, NAME 			VARCHAR2(12) 						CONSTRAINT MM_NM_NN 	NOT NULL
	, NICKNAME 		VARCHAR2(20) 						CONSTRAINT MM_NI_NN 	NOT NULL
	, PASSWORD 		VARCHAR2(16) 						CONSTRAINT MM_PW_NN 	NOT NULL
	, HASBABY		CHAR(1)			DEFAULT 'N'			CONSTRAINT MM_HB_NN		NOT NULL
	, INPUTDATE		DATE			DEFAULT SYSDATE		CONSTRAINT MM_DT_NN		NOT NULL
	, HOMETOWN		VARCHAR2(20)						CONSTRAINT MM_HT_NN		NOT NULL
	, LASTACCESS	DATE			DEFAULT SYSDATE		CONSTRAINT MM_LA_NN		NOT NULL
	, MESSAGE		VARCHAR2(300)	DEFAULT ''
	, PROFILE_ID	NUMBER
);

---------------------
CREATE SEQUENCE BABY_SEQ
START WITH 70; -- SEQUENCE FOR BABY TABLE

CREATE TABLE BABY
(
	BABYNUM			NUMBER										CONSTRAINT BB_BN_PK		PRIMARY KEY
	, NAME			VARCHAR2(18)								CONSTRAINT BB_NM_NN		NOT NULL
	, SEX			CHAR(4)			DEFAULT '여'				CONSTRAINT BB_GD_NN		NOT NULL
	, BORNDATE		DATE			DEFAULT SYSDATE				CONSTRAINT BB_BD_NN		NOT NULL
	, BORNWEEK		VARCHAR2(10)	DEFAULT '10주'				CONSTRAINT BB_BW_NN		NOT NULL
	, DELIVERYINFO	VARCHAR2(30)	DEFAULT '자연분만'			CONSTRAINT BB_DI_NN		NOT NULL
	, BIRTHTYPE		VARCHAR2(30)	DEFAULT '만삭'				CONSTRAINT BB_BT_NN		NOT NULL
	, BIRTHLOCATION	VARCHAR2(25)								CONSTRAINT BB_BL_NN		NOT NULL
	, MEMBEREMAIL	VARCHAR2(25)								CONSTRAINT BB_ME_NN		NOT NULL
	, CONSTRAINT BB_ME_FK	FOREIGN KEY (MEMBEREMAIL)	REFERENCES MAMMAMEMBER (EMAIL) ON DELETE CASCADE
);

----------------- admin ------------------
CREATE SEQUENCE ADMINCHECK_SEQ;

CREATE TABLE ADMINCHECK
(
	ADMINCHECK 		NUMBER											CONSTRAINT AC_AC_PK		PRIMARY KEY
	, EMAIL			VARCHAR2(25)									CONSTRAINT AC_EM_NN		NOT NULL
	, CONSTRAINT AC_AC_FK	FOREIGN KEY (EMAIL)	REFERENCES MAMMAMEMBER ( EMAIL ) ON DELETE CASCADE
);

insert into ADMINCHECK values (1,'admin@mamma.com');

----------------- baby book ------------------

CREATE TABLE BABYINFO
(
	INFONUM			NUMBER										CONSTRAINT BI_IN_PK		PRIMARY KEY
	, HEIGHT		NUMBER(10, 4)								CONSTRAINT BI_HT_NN		NOT NULL
	, WEIGHT		NUMBER(10, 4)								CONSTRAINT BI_WT_NN		NOT NULL
	, HEADSIZE		NUMBER(10, 4)								CONSTRAINT BI_HS_NN		NOT NULL
	, INPUTDATE		DATE										CONSTRAINT BI_DT_NN		NOT NULL
	, BABYNUM		NUMBER										CONSTRAINT BI_BN_NN		NOT NULL
	, CONSTRAINT BI_BN_FK	FOREIGN KEY (BABYNUM)	REFERENCES BABY ( BABYNUM ) ON DELETE CASCADE
);

CREATE SEQUENCE BABYINFO_SEQ; -- SEQUENCE FOR BABYINFO TABLE

---------------------
--BABY 테이블 생성--
create table babyDaily(
	dailynum		number	primary key,
	suyu			number	not null,
	sobyun			number	not null,
	play			number	not null,
	inputdate		date	not null,
	suyuamount	  	number 	default 0,  -----수유 량-------
	liyusik 		number 	not null,
	bochaegi 		number 	not null,
	ungga 			number 	not null,
	sleeptime 		number 	default 0,	-----수면시간 량-----
	diapers 		number 	not null,
	starttime 		date 	not null,
	memo 			varchar2(500) default '특이사항없음'
	, BABYNUM		NUMBER not null
	, CONSTRAINT FR_BD_BN FOREIGN KEY (BABYNUM)	REFERENCES BABY ( BABYNUM ) ON DELETE CASCADE
);

--BABY 시퀀스--
create sequence BABYDAILY_SEQ;

----------------- friends ------------------

CREATE TABLE FRIENDS
(
	FRIEND_ID		NUMBER											CONSTRAINT FR_ID_PK		PRIMARY KEY
	, USER01		VARCHAR2(25)
	, USER02		VARCHAR2(25)
	-- 0: Pending Friend Request
	-- 1: Confirm Friend Request
	-- 2: You
	, STATUS		CHAR(1)			DEFAULT '0'						CONSTRAINT FR_ST_NN		NOT NULL
	, INPUTDATE		DATE			DEFAULT SYSDATE 				CONSTRAINT FR_DT_NN		NOT NULL
	, CONSTRAINT FR_U1_FK	FOREIGN KEY (USER01)	REFERENCES MAMMAMEMBER ( EMAIL ) ON DELETE CASCADE
	, CONSTRAINT FR_U2_FK	FOREIGN KEY (USER02)	REFERENCES MAMMAMEMBER ( EMAIL ) ON DELETE CASCADE
);

CREATE SEQUENCE FRIENDS_SEQ;


----------------- schedule ------------------

CREATE TABLE INFORMATION
(
	ID				NUMBER											CONSTRAINT IF_ID_PK		PRIMARY KEY
	, INFONAME		VARCHAR2(100)									CONSTRAINT IF_NM_NN		NOT NULL
	, INFOCONTENT	VARCHAR2(3000)									CONSTRAINT IF_CT_NN		NOT NULL
	, STARTDATE		DATE											CONSTRAINT IF_SD_NN		NOT NULL
	, ENDDATE		DATE
	, IMPORTANCE	NUMBER			DEFAULT 1
	, ADMINID		VARCHAR2(25)									CONSTRAINT IF_AI_NN		NOT NULL
);

CREATE SEQUENCE INFORMATION_SEQ; -- SEQUENCE FOR INFORMATION TABLE

---------------------
CREATE TABLE VACCINATION
(
	ID				NUMBER											CONSTRAINT VC_ID_PK		PRIMARY KEY
	, TITLE			VARCHAR2(300)									CONSTRAINT VC_TT_NN		NOT NULL
	, CONTENT		VARCHAR2(1000)									CONSTRAINT VC_CT_NN		NOT NULL
	, STARTMONTH	NUMBER											CONSTRAINT VC_SD_NN		NOT NULL
	, ENDMONTH		NUMBER											CONSTRAINT VC_ED_NN		NOT NULL
);

CREATE SEQUENCE VACCINATION_SEQ; -- SEQUENCE FOR VACCINATION TABLE

---------------------

CREATE TABLE banner
(
	id number NOT NULL,
	link varchar2(1000),
	savedFile varchar2(100),
	originalFile varchar2(200),
	PRIMARY KEY (id)
);

insert into banner values(1, ' ', ' ', ' ');
insert into banner values(2, ' ', ' ', ' ');

---------------------
CREATE TABLE CALENDAR
(
	ID				NUMBER											CONSTRAINT CL_ID_PK		PRIMARY KEY
	, INFONAME		VARCHAR2(300)									CONSTRAINT CL_IN_NN		NOT NULL
	, INFOCONTENT	VARCHAR2(3000)									CONSTRAINT CL_IC_NN		NOT NULL
	, STARTDATE		DATE											CONSTRAINT CL_SD_NN		NOT NULL
	, ENDDATE		DATE
	, EMAIL			VARCHAR2(25)									CONSTRAINT CL_EM_NN		NOT NULL
	, CONSTRAINT CL_EM_FK	FOREIGN KEY (EMAIL)	REFERENCES MAMMAMEMBER (EMAIL) ON DELETE CASCADE
);

CREATE SEQUENCE CALENDAR_SEQ; -- SEQUENCE FOR CALENDAR TABLE



----------------- photo ------------------

CREATE TABLE PHOTO
(
	ID				NUMBER											CONSTRAINT PT_ID_PK		PRIMARY KEY
	, CONTENT		VARCHAR2(1000)
	, NICKNAME		VARCHAR2(20)									CONSTRAINT PT_NI_NN		NOT NULL
	, INPUTDATE		DATE			DEFAULT SYSDATE 				CONSTRAINT PT_DT_NN	 	NOT NULL
	-- 사진의 종류
	, KIND			VARCHAR2(100)	DEFAULT 'default'				CONSTRAINT PT_KN_NN		NOT NULL
	, LIKEIT		NUMBER			DEFAULT 0						CONSTRAINT PT_LI_NN		NOT NULL
	, SAVEDFILE		VARCHAR2(100)
	, ORIGINALFILE	VARCHAR2(200)
	, EMAIL			VARCHAR2(25)									CONSTRAINT PT_EM_NN		NOT NULL
	, CONSTRAINT PT_EM_FK	FOREIGN KEY (EMAIL)	REFERENCES MAMMAMEMBER ( EMAIL ) ON DELETE CASCADE
);

CREATE SEQUENCE PHOTO_SEQ; -- SEQUENCE FOR PHOTO TABLE

---------------------
CREATE TABLE PHOTOCOMMENT
(
	PHOTOCOMMENTNUM		NUMBER												CONSTRAINT PC_NM_PK	PRIMARY KEY
	, CONTENT			VARCHAR2(1000)										CONSTRAINT PC_CT_NN NOT NULL
	, NICKNAME			VARCHAR2(20)										CONSTRAINT PC_NI_NN NOT NULL
	, INPUTDATE			DATE			DEFAULT SYSDATE						CONSTRAINT PC_DT_NN NOT NULL
	, LIKEIT			NUMBER			DEFAULT 0							CONSTRAINT PC_LI_NN	NOT NULL
	, ID				NUMBER												CONSTRAINT PC_ID_NN NOT NULL
	, EMAIL				VARCHAR2(25)										CONSTRAINT PC_EM_NN NOT NULL
	, CONSTRAINT PC_EM_FK	FOREIGN KEY (EMAIL)	REFERENCES MAMMAMEMBER ( EMAIL ) ON DELETE CASCADE
	, CONSTRAINT PC_ID_FK	FOREIGN KEY (ID)	REFERENCES PHOTO ( ID ) 		 ON DELETE CASCADE
);

CREATE SEQUENCE PHOTOCOMMENT_SEQ; -- SEQUENCE FOR PHOTO_COMMENT TABLE


----------------- sale ------------------


CREATE SEQUENCE SALEBOARD_SEQ; -- SEQUENCE FOR SALEBOARD TABLE

---------------------


CREATE SEQUENCE SALEBOARDCOMMENT_SEQ; -- SEQUENCE FOR SALEBOARD TABLE

----------------- board ------------------
CREATE TABLE board
(
	boardNum number NOT NULL,
	state varchar2(20),
	title varchar2(300),
	nickname varchar2(20),
	content varchar2(3000),
	viewcount number,
	inputdate date DEFAULT SYSDATE,
	email varchar2(25) NOT NULL,
	PRIMARY KEY (boardNum)
);

CREATE SEQUENCE BOARD_SEQ; -- SEQUENCE FOR BOARD TABLE

---------------------
CREATE TABLE BOARDCOMMENT
(
	COMMENTNUM			NUMBER											 	CONSTRAINT BC_CN_PK PRIMARY KEY
	, NICKNAME			VARCHAR2(20)										CONSTRAINT BC_NI_NN NOT NULL
	, CONTENT			VARCHAR2(3000)										CONSTRAINT BC_CT_NN NOT NULL
	, INPUTDATE			DATE			DEFAULT SYSDATE						CONSTRAINT BC_DT_NN NOT NULL
	, EMAIL				VARCHAR2(25)										CONSTRAINT BC_EM_NN NOT NULL
	, savedFile 		varchar2(100)
	, BOARDNUM			NUMBER												CONSTRAINT BC_BN_NN NOT NULL
	, CONSTRAINT BC_EM_FK	FOREIGN KEY (EMAIL)	REFERENCES MAMMAMEMBER ( EMAIL ) ON DELETE CASCADE
	, CONSTRAINT BC_BN_FK	FOREIGN KEY (BOARDNUM)	REFERENCES BOARD (BOARDNUM) ON DELETE CASCADE
);

CREATE SEQUENCE BOARDCOMMENT_SEQ; -- SEQUENCE FOR BOARDCOMMENT TABLE


---------------------
CREATE TABLE UPDATES
(
	ID					NUMBER												CONSTRAINT UD_ID_PK PRIMARY KEY
	, CONTENT			VARCHAR2(1000)										CONSTRAINT UD_CT_NN NOT NULL
	, INPUTDATE			DATE			DEFAULT SYSDATE						CONSTRAINT UD_DT_NN NOT NULL
	-- 0: not checked
	-- 1: checked
	, STATUS			CHAR(1)			DEFAULT '0' 						CONSTRAINT UD_ST_NN NOT NULL
	, EMAIL				VARCHAR2(25)										CONSTRAINT UD_EM_NN NOT NULL
	, CONSTRAINT UD_EM_FK	FOREIGN KEY (EMAIL)	REFERENCES MAMMAMEMBER ( EMAIL ) ON DELETE CASCADE
);

CREATE SEQUENCE UPDATES_SEQ;
